'''
Created on 2012-4-6下午2:53:41

@author: nagat
@email:  yasenagat@gmail.com
'''
import sqlite3
#To use the module, you must first create a Connection object that represents the database. 


def createTable():
#    You can also supply the special name :memory: to create a database in RAM.
#    采用特殊名称:memory:可以建在内存中
    conn = sqlite3.connect('sqlite/example')
    print(conn,'建立成功')
    
    #cursor必须要有，否则，你啥也干不了
    c = conn.cursor()

    # 建表
    c.execute('create table test (id int, name text)')
    
    # 增加一行
    c.execute("insert into test values (1,'jack')")
    
    # 提交
    conn.commit()
    
    # 关闭
    c.close()
    conn.close()
    
def find():
    conn = sqlite3.connect('sqlite/example')
    c = conn.cursor()
    c.execute('select * from test')
    res = c.fetchall()
    for row in res:
        print(row)
    
    
def dropTable():
    conn = sqlite3.connect('sqlite/example')
    c = conn.cursor()
    c.execute('drop table test')
    conn.commit()
    c.close()
    conn.close()
    
#要使用绑定变量的方法，不要使用字符串拼接的方式
def findById(id):
    conn = sqlite3.connect('sqlite/example')
    c = conn.cursor()
    
    c.execute('select * from test where id = ? ',(id,))
    print(c.fetchall())
    
    
def insert():
    conn = sqlite3.connect('sqlite/example')
    c = conn.cursor()
    c.execute("insert into test values (2,'tom')")
    conn.commit()
    c.close()
    conn.close()
    
#批量插入，使用变量绑定
def insertLarge():
    conn = sqlite3.connect('sqlite/example')
    c = conn.cursor()
    
    for r in [(3, 'lily'),(4, 'cati')]:
        c.execute("insert into test values (?,?)",r)
        
    conn.commit()
    c.close()
    conn.close()   
     
#dropTable()
#createTable()
#insert()
#findById(2)
#insertLarge()


find()
